**Housekeeping
clear all
cap log close
log using ${rep_root}/logs/makepanel_monthly_mid.log, text replace
set more off

********************
*BRING IN COMPLAINTS DATA: MAKE BALANCED PANEL OF OFFICERS WHO APPEAR IN COMPLAINTS DATA 
********************
use ${rep_root}/data/FOIA/FOIA_unified/complaints.dta

**Drop to one unique record per officer
drop if mid == . 
duplicates drop mid, force

**Extract Stata 'yearmonth' date variable from incident date
gen year = year(inc_complaint_dt_1)
gen mo = month(inc_complaint_dt_1)
gen yearmo = ym(year, mo)
format %tm yearmo

keep mid yearmo

**Is this necessary?
sort mid yearmo
by mid yearmo: keep if _n == 1

**Create a record for each possible combination of officer and yearmonth in the data
tsset mid yearmo
drop if yearmo == .
tsfill, full 
drop if mid == . 

********************
*MERGE IN PROFILE
********************

merge m:1 mid using ${rep_root}/data/FOIA/FOIA_unified/profiles.dta
drop if _merge == 2
drop _merge
count if mid==44497
***Get appointment date in yearmonth form
gen app_yearmo = ym(appoint_yr, appoint_m)
format %tm app_yearmo

//drop yearmo before appointed dates

drop if yearmo<app_yearmo & app_yearmo!=. 
count if mid==44497

**Extract resignation yearmonth
gen resig_date = resignationdate
replace resig_date = "" if resignationdate == "NA"
gen resig_date_temp = date(resig_date, "DMY", 2025)
gen resig_year = year(resig_date_temp)
gen resig_mo = month(resig_date_temp)
gen resig_yearmo = ym(resig_year, resig_mo)
format %tm resig_yearmo

drop resig_date resig_date_temp resig_year resig_mo

//drop yearmo after resignation dates
drop if yearmo>resig_yearmo 

**Save a dataset that is every yearmonth a given officer worked
save ${rep_root}/data/monthly_data, replace

********************
*PROCESS AWARDS DATA
********************
use ${rep_root}/data/FOIA/FOIA_unified/awards.dta, clear


**Gen yearmonth for award date
gen tempdate = date(incidentstartdate, "DMY", 2025)
gen year = year(tempdate)
gen mo = month(tempdate)
gen yearmo = ym(year, mo)
format %tm yearmo

gen award_cat = ""
replace award_cat = "merit" if inlist(awardtype, "ANNUAL BUREAU AWARD OF RECOGNITION", "ARNOLD MIRELES SPECIAL PARTNER", "CHICAGO POLICE LEADERSHIP AWARD", "DEPARTMENT COMMENDATION", "DEPLOYMENT OPERATIONS CENTER AWARD", "HONORABLE MENTION", "HONORABLE MENTION RIBBON AWARD", "JOINT OPERATIONS AWARD")
replace award_cat = "merit" if inlist(awardtype, "OUTSIDE GOVERNMENTAL AGENCY AWARD", "POLICE OFFICER OF THE MONTH AWARD", "PROBLEM SOLVING AWARD", "RECOGNITION / OUTSIDE GOVERNMENTAL AGENCY AWARD", "SPECIAL COMMENDATION", "SPECIAL HONORABLE MENTION", "SPECIAL SERVICE AWARD", "SUPERINTENDENT'S HONORABLE MENTION")
replace award_cat = "merit" if inlist(awardtype, "SUPERINTENDENTS AWARD OF MERIT", "TOP GUN ARREST AWARD", "TRAFFIC STOP OF THE MONTH AWARD", "UNIT MERITORIOUS PERFORMANCE AWARD")

replace award_cat = "valor" if inlist(awardtype, "CARTER HARRISON", "HUNDRED CLUB OF COOK COUNTY MEDAL OF VALOR", "LAMBERT TREE", "LIFE SAVING AWARD", "POLICE BLUE SHIELD AWARD", "POLICE BLUE STAR AWARD", "POLICE MEDAL", "SUPERINTENDENT'S AWARD OF TACTICAL EXCELLENCE", "SUPERINTENDENTS AWARD OF VALOR")

replace award_cat = "routine" if inlist(awardtype, "ATTENDANCE RECOGNITION AWARD", "EMBLEM OF RECOGNITION - APPEARANCE", "EMBLEM OF RECOGNITION - PHYSICAL FITNESS")

replace award_cat = "affiliation" if inlist(awardtype, "FIELD TRAINING SERVICE AWARD", "HONOR GUARD AWARD", "MILITARY DEPLOYMENT AWARD", "MILITARY SERVICE AWARD", "PIPE BAND AWARD", "THOMAS WORTHAM IV MILITARY AND COMMUNITY SERVICE AWARD")

replace award_cat = "event" if inlist(awardtype, "2009 CRIME REDUCTION AWARD", "2019 CRIME REDUCTION AWARD", "NATO SUMMIT SERVICE AWARD", "PRESIDENTIAL ELECTION DEPLOYMENT AWARD 2008")

replace award_cat = "non-officer" if inlist(awardtype, "AWARD OF APPRECIATION", "PAUL R. BAUER LEADERSHIP AWARD", "WILLIAM POWERS LEADERSHIP AWARD")

replace award_cat = "other" if inlist(awardtype, "OTHER AWARDS", "COMPLIMENTARY LETTER")

tab award_cat, m

gen awards= 1
gen merit = award_cat=="merit"
gen merit_valor = inlist(award_cat, "merit", "valor")
gen merit_valor_routine =inlist(award_cat, "merit", "valor", "routine")

**Total awards within a given yearmonth for a given officer
collapse (sum) awards merit merit_valor merit_valor_routine, by(mid yearmo) 

**Ignore data before complaints data start
keep if yearmo>=ym(2004,1)

tempfile tempawards
save `tempawards'

 
********************
*PROCESS TRR DATA
******************** 
use ${rep_root}/data/FOIA/FOIA_unified/TRR.dta, clear 

duplicates drop trr_id, force

gen force = 1 
gen sub_inj = subject_injured == "Yes"

 **Generate yearmonth for force date
gen year = yofd(dofc(datetime))
gen mo = month(dofc(datetime))
gen yearmo = ym(year, mo)
format %tm yearmo

**Total force and injury incidents within a given yearmonth for a given officer
collapse (sum) force (sum) sub_inj, by(mid yearmo)


tempfile tempforce
save `tempforce'

********************
*PROCESS SALARY DATA
******************** 
use ${rep_root}/data/FOIA/FOIA_unified/salary.dta, clear 
keep mid JobTitle PayGradeName year

**Save yearly paygrade
sort mid year PayGrade
by mid year: keep if _n == _N

tempfile tempsalary
save `tempsalary'


********************
*PROCESS UNIT HISTORY DATA
******************** 
use ${rep_root}/data/FOIA/FOIA_unified/unithistory.dta, clear 

**Clean data a bit
drop if mid == . 
duplicates drop 
drop if ASSIGNED_EFFECTIVE_DATE==.
 
 *Extract assignment yearmonth 
gen year = year(ASSIGNED_EFFECTIVE_DATE)
gen month = month(ASSIGNED_EFFECTIVE_DATE)
gen start_yearmo = ym(year, month)
format %tm start_yearmo 
sort mid start_yearmo

//Drop if the officer spent less than a month and transitioned to a different unit
**Most of these records have zero length, indicating duplications in the database
sort mid ASSIGNED_EFFECTIVE_DATE
by mid: gen assignment_length = ASSIGNED_EFFECTIVE_DATE[_n+1] - ASSIGNED_EFFECTIVE_DATE
drop if assignment_length<31

sort mid start_yearmo
rename start_yearmo yearmo

keep mid yearmo UNIT_ASSIGNED_TO
rename UNIT_ASSIGNED_TO unit

tempfile tempunit
save `tempunit'




********************
*MERGE TO MONTHLY PANEL
******************** 
use ${rep_root}/data/monthly_data, clear

**Merge on awards, setting award count to 0 if not observed
merge 1:1 mid yearmo using `tempawards'
drop if _merge == 2

replace awards = 0 if _merge == 1 & yearmo>=ym(2004,1)
replace merit = 0 if _merge == 1 & yearmo>=ym(2004,1)
replace merit_valor = 0 if _merge == 1 & yearmo>=ym(2004,1)
replace merit_valor_routine = 0 if _merge == 1 & yearmo>=ym(2004,1)

drop _merge

**Merge on force, setting force count to 0 if not observed
merge 1:1 mid yearmo using `tempforce'
drop if _merge == 2

replace force = 0 if _merge == 1 & yearmo>=ym(2004,1) & yearmo<=ym(2019,7)
replace sub_inj = 0 if _merge == 1 & yearmo>=ym(2004,1) & yearmo<=ym(2019,7)
drop _merge

**Merge on yearly salary data
gen year = yofd(dofm(yearmo))
merge m:1 year mid using `tempsalary'

drop if _merge == 2
drop _merge

**Merge on unit assignment data
merge 1:1 mid yearmo using `tempunit'

sort mid yearmo 

**Backfill unit data from point of assignment
replace unit = unit[_n-1] if unit == . & mid == mid[_n-1]
drop if _merge == 2
drop _merge


********************
*CREATE VARIABLES FOR UNIT CHANGE, PROMOTION
******************** 
sort mid yearmo

//changed unit within 12 months
gen unit_change12 = 0 if unit !=.
forval i = 1/12{
replace unit_change12 = 1 if unit != unit[_n-`i'] & unit !=.& unit[_n-`i'] !=.& mid == mid[_n-`i']
}
 
gen level = 1 if PayGradeName =="D|1"
replace level = 2 if inlist(PayGradeName,"D|2", "D|3") //specialists
replace level = 3 if inlist(PayGradeName,"D|2A") //detectives 
replace level = 4 if inlist(PayGradeName,"E|3") //sergeants
replace level =5 if inlist(PayGradeName,"E|4")  
replace level =6 if inlist(PayGradeName,"E|5")  
replace level =7 if inlist(PayGradeName,"E|6")  
replace level =8 if inlist(PayGradeName,"E|7")
replace level =9 if inlist(PayGradeName,"SR|9752","SR|9785","SR|9796", "EX|9011", "EX|9684", "EX|9781","EX|9782", "EX|9957") //leadership

**Increased salary level within 12 months
gen promoted12 = 0 if PayGradeName !=""
forval i = 1/12{
replace promoted12 = 1 if level > level[_n-`i'] & level !=.& level[_n-`i'] !=. & mid == mid[_n-`i']
}

 
**Save unified panel of outcomes
save ${rep_root}/data/mid_panel, replace
 
clear
log close


